
# em geral, prefiro colocar todos os imports em um lugar só
import numpy as np
import pandas as pd
import statistics
import collections
import nltk
import string
import matplotlib.pyplot as plt

import plotly.express as px
import plotly.graph_objs as go
import plotly.offline as py
import plotly.graph_objs as go
import plotly.figure_factory as ff

from sklearn.model_selection import cross_val_predict
from sklearn.model_selection import cross_val_score
from sklearn.preprocessing import StandardScaler
from sklearn.preprocessing import MinMaxScaler
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from sklearn.linear_model import Ridge, Lasso
from sklearn.tree import DecisionTreeRegressor
from sklearn.ensemble import RandomForestRegressor
from sklearn.svm import SVR
from sklearn.metrics import mean_absolute_error, mean_squared_error, r2_score 
from sklearn.metrics import mean_absolute_percentage_error, make_scorer

from nltk.tokenize import word_tokenize
from nltk.corpus import stopwords

from statistics import mean
from scipy import stats

# import scikit_posthocs as sp

py.init_notebook_mode(connected=True)


nome_do_arquivo = "C:/Users/elain/Desktop/LIGHTHOUSE/teste_indicium_precificacao.csv"
dados = pd.read_csv(nome_do_arquivo)


dados.shape

(48894, 16)


dados.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 48894 entries, 0 to 48893
Data columns (total 16 columns):
 #   Column                         Non-Null Count  Dtype  
---  ------                         --------------  -----  
 0   id                             48894 non-null  int64  
 1   nome                           48878 non-null  object 
 2   host_id                        48894 non-null  int64  
 3   host_name                      48873 non-null  object 
 4   bairro_group                   48894 non-null  object 
 5   bairro                         48894 non-null  object 
 6   latitude                       48894 non-null  float64
 7   longitude                      48894 non-null  float64
 8   room_type                      48894 non-null  object 
 9   price                          48894 non-null  int64  
 10  minimo_noites                  48894 non-null  int64  
 11  numero_de_reviews              48894 non-null  int64  
 12  ultima_review                  38842 non-null  object 
 13  reviews_por_mes                38842 non-null  float64
 14  calculado_host_listings_count  48894 non-null  int64  
 15  disponibilidade_365            48894 non-null  int64  
dtypes: float64(3), int64(7), object(6)
memory usage: 6.0+ MB


dados.describe()


dados.tail(10)


dados.head(10)


colunas_nan = dados.columns[dados.isna().any()].tolist()
colunas_nan

['nome', 'host_name', 'ultima_review', 'reviews_por_mes']


print(dados[colunas_nan])

                                                    nome      host_name  \
0                                  Skylit Midtown Castle       Jennifer   
1                    THE VILLAGE OF HARLEM....NEW YORK !      Elisabeth   
2                        Cozy Entire Floor of Brownstone    LisaRoxanne   
3       Entire Apt: Spacious Studio/Loft by central park          Laura   
4              Large Cozy 1 BR Apartment In Midtown East          Chris   
...                                                  ...            ...   
48889    Charming one bedroom - newly renovated rowhouse        Sabrina   
48890      Affordable room in Bushwick/East Williamsburg        Marisol   
48891            Sunny Studio at Historical Neighborhood  Ilgar & Aysel   
48892               43rd St. Time Square-cozy single bed            Taz   
48893  Trendy duplex in the very heart of Hell's Kitchen     Christophe   

      ultima_review  reviews_por_mes  
0        2019-05-21             0.38  
1               NaN              NaN  
2        2019-07-05             4.64  
3        2018-11-19             0.10  
4        2019-06-22             0.59  
...             ...              ...  
48889           NaN              NaN  
48890           NaN              NaN  
48891           NaN              NaN  
48892           NaN              NaN  
48893           NaN              NaN  

[48894 rows x 4 columns]


linhas_nan = dados[dados.isna().any(axis=1)]
print(linhas_nan)

             id                                               nome   host_id  \
1          3647                THE VILLAGE OF HARLEM....NEW YORK !      4632   
18         7750                  Huge 2 BR Upper East  Cental Park     17985   
25         8700  Magnifique Suite au N de Manhattan - vue Cloitres     26394   
35        11452                        Clean and Quiet in Brooklyn      7355   
37        11943                          Country space in the city     45445   
...         ...                                                ...       ...   
48889  36484665    Charming one bedroom - newly renovated rowhouse   8232441   
48890  36485057      Affordable room in Bushwick/East Williamsburg   6570630   
48891  36485431            Sunny Studio at Historical Neighborhood  23492952   
48892  36485609               43rd St. Time Square-cozy single bed  30985759   
48893  36487245  Trendy duplex in the very heart of Hell's Kitchen  68119814   

             host_name bairro_group              bairro  latitude  longitude  \
1            Elisabeth    Manhattan              Harlem  40.80902  -73.94190   
18                Sing    Manhattan         East Harlem  40.79685  -73.94872   
25     Claude & Sophie    Manhattan              Inwood  40.86754  -73.92639   
35                  Vt     Brooklyn  Bedford-Stuyvesant  40.68876  -73.94312   
37             Harriet     Brooklyn            Flatbush  40.63702  -73.96327   
...                ...          ...                 ...       ...        ...   
48889          Sabrina     Brooklyn  Bedford-Stuyvesant  40.67853  -73.94995   
48890          Marisol     Brooklyn            Bushwick  40.70184  -73.93317   
48891    Ilgar & Aysel    Manhattan              Harlem  40.81475  -73.94867   
48892              Taz    Manhattan      Hell's Kitchen  40.75751  -73.99112   
48893       Christophe    Manhattan      Hell's Kitchen  40.76404  -73.98933   

             room_type  price  minimo_noites  numero_de_reviews ultima_review  \
1         Private room    150              3                  0           NaN   
18     Entire home/apt    190              7                  0           NaN   
25        Private room     80              4                  0           NaN   
35        Private room     35             60                  0           NaN   
37        Private room    150              1                  0           NaN   
...                ...    ...            ...                ...           ...   
48889     Private room     70              2                  0           NaN   
48890     Private room     40              4                  0           NaN   
48891  Entire home/apt    115             10                  0           NaN   
48892      Shared room     55              1                  0           NaN   
48893     Private room     90              7                  0           NaN   

       reviews_por_mes  calculado_host_listings_count  disponibilidade_365  
1                  NaN                              1                  365  
18                 NaN                              2                  249  
25                 NaN                              1                    0  
35                 NaN                              1                  365  
37                 NaN                              1                  365  
...                ...                            ...                  ...  
48889              NaN                              2                    9  
48890              NaN                              2                   36  
48891              NaN                              1                   27  
48892              NaN                              6                    2  
48893              NaN                              1                   23  

[10074 rows x 16 columns]


dados.isna().sum()

id                                   0
nome                                16
host_id                              0
host_name                           21
bairro_group                         0
bairro                               0
latitude                             0
longitude                            0
room_type                            0
price                                0
minimo_noites                        0
numero_de_reviews                    0
ultima_review                    10052
reviews_por_mes                  10052
calculado_host_listings_count        0
disponibilidade_365                  0
dtype: int64


dados.isnull().sum()

id                                   0
nome                                16
host_id                              0
host_name                           21
bairro_group                         0
bairro                               0
latitude                             0
longitude                            0
room_type                            0
price                                0
minimo_noites                        0
numero_de_reviews                    0
ultima_review                    10052
reviews_por_mes                  10052
calculado_host_listings_count        0
disponibilidade_365                  0
dtype: int64


porcentagem = 16/dados.shape[0]
porcentagem

0.0003272385159733301


porcentagem = 21/dados.shape[0]
porcentagem

0.0004295005522149957


porcentagem = 10052/dados.shape[0]
porcentagem

0.20558759766024462


fig = go.Figure(data=[go.Histogram(x=dados['reviews_por_mes'])])
fig.update_layout(title='Histograma',
                 xaxis_title='Valores',
                 yaxis_title='Frequencia')
fig.show()


fig = go.Figure(data=[go.Histogram(x=dados['ultima_review'])])
fig.update_layout(title='Histograma',
                 xaxis_title='Valores',
                 yaxis_title='Frequencia')
fig.show()


dados2 = dados.dropna()
dados2.head(10)


statistics.median(dados2['reviews_por_mes'])

0.72


dados['reviews_por_mes'] = dados['reviews_por_mes'].fillna(0.72)
dados.head(10)


dados['ultima_review'] = dados['ultima_review'].fillna('0000-00-00')
dados.head(10)


res = collections.Counter(dados['ultima_review'])
df_res = pd.DataFrame(list(res.items()), columns=['res', 'frequencia'])
df_res.sort_values(by='frequencia', ascending=False)


dados3 = dados[dados['ultima_review'] != '0000-00-00']
dados3.shape

(38842, 16)


datas = pd.to_datetime(dados3['ultima_review'])
datas.tail(10)

48633   2019-07-05
48635   2019-07-06
48700   2019-07-08
48731   2019-07-06
48739   2019-07-07
48781   2019-07-07
48789   2019-07-07
48798   2019-07-07
48804   2019-07-07
48851   2019-07-08
Name: ultima_review, dtype: datetime64[ns]


datas.median()

Timestamp('2019-05-19 00:00:00')


dados['ultima_review'] = dados['ultima_review'].replace("0000-00-00", "2019-05-19")
dados.head(10)


dados[['ano', 'mes', 'dia']] = dados['ultima_review'].str.split('-', expand=True)
dados.head(10)


dados.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 48894 entries, 0 to 48893
Data columns (total 19 columns):
 #   Column                         Non-Null Count  Dtype  
---  ------                         --------------  -----  
 0   id                             48894 non-null  int64  
 1   nome                           48878 non-null  object 
 2   host_id                        48894 non-null  int64  
 3   host_name                      48873 non-null  object 
 4   bairro_group                   48894 non-null  object 
 5   bairro                         48894 non-null  object 
 6   latitude                       48894 non-null  float64
 7   longitude                      48894 non-null  float64
 8   room_type                      48894 non-null  object 
 9   price                          48894 non-null  int64  
 10  minimo_noites                  48894 non-null  int64  
 11  numero_de_reviews              48894 non-null  int64  
 12  ultima_review                  48894 non-null  object 
 13  reviews_por_mes                48894 non-null  float64
 14  calculado_host_listings_count  48894 non-null  int64  
 15  disponibilidade_365            48894 non-null  int64  
 16  ano                            48894 non-null  object 
 17  mes                            48894 non-null  object 
 18  dia                            48894 non-null  object 
dtypes: float64(3), int64(7), object(9)
memory usage: 7.1+ MB


colunas = ['ano', 'dia', 'mes']
dados[colunas] = dados[colunas].astype(int)
dados.head(10)


dados['ultima_review'] = pd.to_datetime(dados['ultima_review'])
dados.head(10)


dados.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 48894 entries, 0 to 48893
Data columns (total 19 columns):
 #   Column                         Non-Null Count  Dtype         
---  ------                         --------------  -----         
 0   id                             48894 non-null  int64         
 1   nome                           48878 non-null  object        
 2   host_id                        48894 non-null  int64         
 3   host_name                      48873 non-null  object        
 4   bairro_group                   48894 non-null  object        
 5   bairro                         48894 non-null  object        
 6   latitude                       48894 non-null  float64       
 7   longitude                      48894 non-null  float64       
 8   room_type                      48894 non-null  object        
 9   price                          48894 non-null  int64         
 10  minimo_noites                  48894 non-null  int64         
 11  numero_de_reviews              48894 non-null  int64         
 12  ultima_review                  48894 non-null  datetime64[ns]
 13  reviews_por_mes                48894 non-null  float64       
 14  calculado_host_listings_count  48894 non-null  int64         
 15  disponibilidade_365            48894 non-null  int64         
 16  ano                            48894 non-null  int32         
 17  mes                            48894 non-null  int32         
 18  dia                            48894 non-null  int32         
dtypes: datetime64[ns](1), float64(3), int32(3), int64(7), object(5)
memory usage: 6.5+ MB


for col in dados.columns:
    fig = go.Figure()
    fig.add_trace(go.Scatter(x=dados.index, y=dados[col], mode='lines', name=col))
    fig.update_layout(title=f'Gráfico de Linhas - {col}',
                      xaxis_title='Índice',
                      yaxis_title='Valor')
    py.iplot(fig)


ano_freq = collections.Counter(dados['ano'])
df_ano_freq = pd.DataFrame(list(ano_freq.items()), columns=['ano', 'frequencia'])
df_ano_freq.sort_values(by='ano', ascending=False)


mes_freq = collections.Counter(dados['mes'])
df_mes_freq = pd.DataFrame(list(mes_freq.items()), columns=['mes', 'frequencia'])
df_mes_freq.sort_values(by='mes', ascending=False)


dia_freq = collections.Counter(dados['dia'])
df_dia_freq = pd.DataFrame(list(dia_freq.items()), columns=['dia', 'frequencia'])
df_dia_freq.sort_values(by='dia', ascending=False)


result2 = collections.Counter(dados['nome'])
df = pd.DataFrame(result2.items(), columns=['nome', 'count'])
df_sorted = df.sort_values(by='nome')
df_sorted.to_csv('sorted_result_2.csv', index=False)
df_sorted


bairros_unicos = sorted(dados['bairro'].unique())
df_bairros = pd.DataFrame({'Bairros': bairros_unicos})
print(df_bairros)

             Bairros
0           Allerton
1      Arden Heights
2           Arrochar
3            Arverne
4            Astoria
..               ...
216  Windsor Terrace
217        Woodhaven
218         Woodlawn
219          Woodrow
220         Woodside

[221 rows x 1 columns]


collections.Counter(dados['room_type'])

Counter({'Entire home/apt': 25409, 'Private room': 22325, 'Shared room': 1160})


result = collections.Counter(dados['host_id'])
df = pd.DataFrame(result.items(), columns=['host_id', 'count'])
df_sorted = df.sort_values(by='count', ascending=False)
df_sorted.to_csv('sorted_result.csv', index=False)
df_sorted


# 219517861
filtrado2 = dados[dados['host_id'] == 219517861]
pd.DataFrame(filtrado2)


anuncios_por_host = dados.groupby(['host_id', 'bairro_group'])['nome'].size().reset_index(name='num_anuncios')
anuncios_por_host.sort_values(by='num_anuncios', ascending=False)


anuncios_por_host = dados.groupby(['host_id', 'bairro'])['nome'].size().reset_index(name='num_anuncios')
anuncios_por_host.sort_values(by='num_anuncios', ascending=False)


dados.groupby('room_type')['minimo_noites'].mean()

room_type
Entire home/apt    8.506907
Private room       5.378096
Shared room        6.475000
Name: minimo_noites, dtype: float64


media_preco_por_tipo = dados.groupby('room_type')['price'].mean()
tipo_mais_caro = media_preco_por_tipo.idxmax()
preco_mais_caro = media_preco_por_tipo.max()
tipo_mais_barato = media_preco_por_tipo.idxmin()
preco_mais_barato = media_preco_por_tipo.min()
bairro_mais_caro = dados.loc[dados['room_type'] == tipo_mais_caro, 'bairro'].unique()
bairro_mais_barato = dados.loc[dados['room_type'] == tipo_mais_barato, 'bairro'].unique()
print(f"O tipo de apartamento mais caro é '{tipo_mais_caro}' com preço médio de ${preco_mais_caro:.2f}, localizado nos bairros: {bairro_mais_caro}.")
print()
print(f"O tipo de apartamento mais barato é '{tipo_mais_barato}' com preço médio de ${preco_mais_barato:.2f}, localizado nos bairros: {bairro_mais_barato}.")
print()

O tipo de apartamento mais caro é 'Entire home/apt' com preço médio de $211.79, localizado nos bairros: ['Midtown' 'Clinton Hill' 'East Harlem' 'Murray Hill' 'Chinatown'
 'Upper West Side' 'West Village' 'Williamsburg' 'Fort Greene'
 'Crown Heights' 'Bedford-Stuyvesant' "Hell's Kitchen" 'East Village'
 'Bushwick' 'South Slope' 'Harlem' 'Prospect-Lefferts Gardens'
 'Greenpoint' 'Kips Bay' 'SoHo' 'Chelsea' 'Upper East Side'
 'Prospect Heights' 'Park Slope' 'Lower East Side' 'Flatbush'
 'Brooklyn Heights' 'Carroll Gardens' 'Gowanus' 'Cobble Hill'
 'Boerum Hill' 'Financial District' 'Ridgewood' 'Middle Village'
 'Ditmars Steinway' 'Flatiron District' 'Greenwich Village' 'Little Italy'
 'East Flatbush' 'Astoria' 'Eastchester' 'Washington Heights'
 'Kingsbridge' 'Forest Hills' 'Nolita' 'Windsor Terrace' 'Woodlawn'
 'Gravesend' 'Gramercy' 'Allerton' 'East New York' 'Sheepshead Bay'
 'Theater District' 'Long Island City' 'Fort Hamilton' 'Bensonhurst'
 'Shore Acres' 'Sunset Park' 'DUMBO' 'Elmhurst' 'Sunnyside' 'Arrochar'
 'Kensington' 'Spuyten Duyvil' 'Columbia St' 'Brighton Beach'
 'Vinegar Hill' 'Longwood' 'Inwood' 'Tribeca' 'Civic Center' 'Rego Park'
 'Concourse' 'Arverne' 'Tottenville' 'NoHo' 'Flatlands' 'Woodside'
 'Morningside Heights' 'Bayside' 'Downtown Brooklyn' 'Fieldston'
 'College Point' 'Flushing' 'Briarwood' 'City Island' 'Bay Ridge'
 'East Elmhurst' 'Port Richmond' 'Red Hook' 'St. George' 'Canarsie'
 'Richmond Hill' 'Queens Village' 'Glendale' 'Cypress Hills'
 'Battery Park City' 'Jackson Heights' 'Maspeth' 'Two Bridges' 'Woodrow'
 'Stuyvesant Town' 'Parkchester' 'Port Morris' 'Roosevelt Island'
 'St. Albans' 'Bronxdale' 'Sea Gate' 'Riverdale' 'Kew Gardens Hills'
 'Ozone Park' 'Jamaica' 'Rockaway Beach' 'Whitestone' 'Clifton'
 'Navy Yard' 'Borough Park' 'Williamsbridge' 'Woodhaven' 'Mott Haven'
 'Brownsville' 'Bay Terrace' 'Concourse Village' 'Clason Point'
 'Stapleton' 'Lighthouse Hill' 'Springfield Gardens' 'Tompkinsville'
 'Howard Beach' 'Jamaica Estates' 'Kew Gardens' 'Fresh Meadows' 'Midwood'
 'West Brighton' 'Mariners Harbor' 'Great Kills' 'Mount Hope'
 'Marble Hill' 'Manhattan Beach' 'Dongan Hills' 'East Morrisania'
 'Neponsit' 'Throgs Neck' 'Todt Hill' 'Silver Lake' 'Randall Manor'
 'Concord' 'Morrisania' 'Bellerose' 'Grymes Hill' 'Dyker Heights'
 'Castleton Corners' 'Far Rockaway' 'Pelham Bay' 'Edgemere' 'Wakefield'
 'Midland Beach' 'Baychester' 'Melrose' 'Norwood' 'Rosedale'
 'Pelham Gardens' 'Bergen Beach' 'Cambria Heights' 'Richmondtown' 'Corona'
 'South Ozone Park' 'Claremont Village' 'Tremont' 'University Heights'
 'Howland Hook' 'Fordham' 'Schuylerville' 'New Dorp Beach' 'Coney Island'
 'Van Nest' 'Highbridge' "Prince's Bay" 'South Beach' 'Bath Beach'
 'Eltingville' 'Oakwood' 'Castle Hill' 'North Riverdale' 'Soundview'
 'Douglaston' 'Huguenot' 'Morris Heights' 'Grant City' 'Emerson Hill'
 'Westerleigh' 'Bay Terrace, Staten Island' 'Westchester Square'
 'Fort Wadsworth' 'Laurelton' 'Unionport' 'Mill Basin' 'Belmont' 'Hollis'
 'New Springville' 'Edenwald' 'Arden Heights' 'New Dorp' 'Morris Park'
 'Mount Eden' 'Graniteville' 'Bayswater' 'Rossville' 'Willowbrook'
 'Belle Harbor' 'Rosebank' 'Hunts Point' 'West Farms' 'Jamaica Hills'
 'New Brighton' 'Little Neck'].

O tipo de apartamento mais barato é 'Shared room' com preço médio de $70.13, localizado nos bairros: ['Lower East Side' "Hell's Kitchen" 'East Harlem' 'Harlem'
 'Upper East Side' 'Ozone Park' 'Jamaica' 'Chelsea' 'Upper West Side'
 'SoHo' 'Bedford-Stuyvesant' 'Williamsburg' 'Flushing' 'West Village'
 'Washington Heights' 'Gowanus' 'Astoria' 'Richmond Hill' 'Midtown'
 'Flatbush' 'Bellerose' 'Theater District' 'Battery Park City'
 'Forest Hills' 'Financial District' 'Crown Heights' 'East Village'
 'Kingsbridge' 'Jackson Heights' 'Clinton Hill' 'Fordham' 'Sunset Park'
 'Clason Point' 'Sunnyside' 'Bushwick' 'Long Island City' 'Gramercy'
 'Murray Hill' 'Flatlands' 'Kips Bay' 'Borough Park' 'Eastchester'
 'Sheepshead Bay' 'Roosevelt Island' 'East Flatbush' 'Greenpoint'
 'Pelham Bay' 'Corona' 'East New York' 'Bensonhurst' 'Ditmars Steinway'
 'Gravesend' 'Inwood' 'Little Italy' 'Chinatown' 'Kew Gardens'
 'Parkchester' 'Morningside Heights' 'Fort Hamilton' 'Midwood'
 'Boerum Hill' 'Nolita' 'Fort Greene' 'Prospect-Lefferts Gardens'
 'Park Slope' 'Brighton Beach' 'Vinegar Hill' 'Cypress Hills' 'Canarsie'
 'Coney Island' 'Bay Ridge' 'Rego Park' 'Hollis' 'Schuylerville'
 'East Elmhurst' 'Port Morris' 'Kensington' 'Ridgewood' 'Woodlawn'
 'Belmont' 'Riverdale' 'Concourse Village' 'Wakefield' 'Queens Village'
 'Greenwich Village' 'Red Hook' 'Elmhurst' 'South Slope' 'South Beach'
 'Morris Heights' 'Woodhaven' 'Olinville' 'Norwood' 'Briarwood' 'Maspeth'
 'Clifton' 'South Ozone Park' 'Springfield Gardens' 'Morris Park'
 'Bay Terrace' 'Dyker Heights' 'Fieldston' 'Little Neck' 'Van Nest'
 'University Heights' 'Tremont' 'Morrisania' 'Concord' 'Woodside'
 'Pelham Gardens' 'Longwood' 'Randall Manor' 'Bronxdale' 'Stuyvesant Town'
 'Arverne'].


total_reviews_por_bairro = dados.groupby('bairro')['numero_de_reviews'].sum()
bairro_mais_reviews = total_reviews_por_bairro.idxmax()
max_reviews = total_reviews_por_bairro.max()
bairro_menos_reviews = total_reviews_por_bairro.idxmin()
min_reviews = total_reviews_por_bairro.min()
print(f"O bairro com o maior número total de reviews é '{bairro_mais_reviews}' com {max_reviews} reviews.")
print(f"O bairro com o menor número total de reviews é '{bairro_menos_reviews}' com {min_reviews} reviews.")

O bairro com o maior número total de reviews é 'Bedford-Stuyvesant' com 110352 reviews.
O bairro com o menor número total de reviews é 'Fort Wadsworth' com 0 reviews.


maior_reviews = dados.loc[dados['numero_de_reviews'].idxmax()]
print("\nAnuncio com o maior número de reviews:")
print(maior_reviews[['nome', 'bairro', 'bairro_group','numero_de_reviews']])
menor_reviews = dados.loc[dados['numero_de_reviews'].idxmin()]
print("\nAnuncio com o menor número de reviews:")
print(menor_reviews[['nome', 'bairro', 'bairro_group', 'numero_de_reviews']])

Anuncio com o maior número de reviews:
nome                 Room near JFK Queen Bed
bairro                               Jamaica
bairro_group                          Queens
numero_de_reviews                        629
Name: 11758, dtype: object

Anuncio com o menor número de reviews:
nome                 THE VILLAGE OF HARLEM....NEW YORK !
bairro                                            Harlem
bairro_group                                   Manhattan
numero_de_reviews                                      0
Name: 1, dtype: object


anuncios_por_bairro = dados.groupby('bairro')['nome'].nunique().reset_index(name='num_tipos_anuncios')
anuncios_por_bairro = anuncios_por_bairro.sort_values(by='num_tipos_anuncios', ascending=False)
print(anuncios_por_bairro)

                 bairro  num_tipos_anuncios
214        Williamsburg                3830
13   Bedford-Stuyvesant                3674
94               Harlem                2634
28             Bushwick                2434
202     Upper West Side                1948
..                  ...                 ...
82       Fort Wadsworth                   1
215         Willowbrook                   1
165        Richmondtown                   1
172           Rossville                   1
141            New Dorp                   1

[221 rows x 2 columns]


preco_maximo = dados['price'].max()
preco_minimo = dados['price'].min()
print("Preço máximo:", preco_maximo)
print("Preço mínimo:", preco_minimo)

Preço máximo: 10000
Preço mínimo: 0


preco_medio_por_bairro = dados.groupby('bairro')['price'].mean().reset_index()
preco_medio_por_bairro = preco_medio_por_bairro.sort_values(by='price', ascending=False)
print(preco_medio_por_bairro)

             bairro       price
82   Fort Wadsworth  800.000000
219         Woodrow  700.000000
197         Tribeca  490.638418
174        Sea Gate  487.857143
167       Riverdale  442.090909
..              ...         ...
141        New Dorp   57.000000
179       Soundview   53.466667
196         Tremont   51.545455
102     Hunts Point   50.500000
27      Bull's Head   47.333333

[221 rows x 2 columns]


ganho_medio_por_noite = dados.groupby('host_id')['price'].mean().reset_index(name='ganho_medio_por_noite')
ganho_medio_por_ano = ganho_medio_por_noite['ganho_medio_por_noite'] * 365

ganho_medio_por_ano = pd.DataFrame({'host_id': ganho_medio_por_noite['host_id'], 'ganho_medio_por_ano': ganho_medio_por_ano})

ganho_medio_por_noite = ganho_medio_por_noite.sort_values(by='ganho_medio_por_noite', ascending=False)
ganho_medio_por_ano = ganho_medio_por_ano.sort_values(by='ganho_medio_por_ano', ascending=False)

print("Ganho médio por noite para cada host:")
print(ganho_medio_por_noite)

print("\nGanho médio por ano para cada host:")
print(ganho_medio_por_ano)

Ganho médio por noite para cada host:
        host_id  ganho_medio_por_noite
7693    5143901                10000.0
26778  72390391                10000.0
16313  20582832                10000.0
6315    3906464                 9999.0
2516    1235070                 9999.0
...         ...                    ...
1609     647528                   10.0
23429  47336995                   10.0
25797  62685070                   10.0
11495  10132166                    0.0
13414  13709292                    0.0

[37457 rows x 2 columns]

Ganho médio por ano para cada host:
        host_id  ganho_medio_por_ano
7693    5143901            3650000.0
26778  72390391            3650000.0
16313  20582832            3650000.0
6315    3906464            3649635.0
2516    1235070            3649635.0
...         ...                  ...
1609     647528               3650.0
23429  47336995               3650.0
25797  62685070               3650.0
11495  10132166                  0.0
13414  13709292                  0.0

[37457 rows x 2 columns]


indice_disponibilidade_maxima = dados['disponibilidade_365'].idxmax()
indice_disponibilidade_minima = dados['disponibilidade_365'].idxmin()

anuncio_disponibilidade_maxima = dados.loc[indice_disponibilidade_maxima]
anuncio_disponibilidade_minima = dados.loc[indice_disponibilidade_minima]

print("Anúncio com a disponibilidade mais alta:")
print(anuncio_disponibilidade_maxima)

print("\nAnúncio com a disponibilidade mais baixa:")
print(anuncio_disponibilidade_minima)

Anúncio com a disponibilidade mais alta:
id                                                              3647
nome                             THE VILLAGE OF HARLEM....NEW YORK !
host_id                                                         4632
host_name                                                  Elisabeth
bairro_group                                               Manhattan
bairro                                                        Harlem
latitude                                                    40.80902
longitude                                                   -73.9419
room_type                                               Private room
price                                                            150
minimo_noites                                                      3
numero_de_reviews                                                  0
ultima_review                                    2019-05-19 00:00:00
reviews_por_mes                                                 0.72
calculado_host_listings_count                                      1
disponibilidade_365                                              365
ano                                                             2019
mes                                                                5
dia                                                               19
Name: 1, dtype: object

Anúncio com a disponibilidade mais baixa:
id                                                                           5022
nome                             Entire Apt: Spacious Studio/Loft by central park
host_id                                                                      7192
host_name                                                                   Laura
bairro_group                                                            Manhattan
bairro                                                                East Harlem
latitude                                                                 40.79851
longitude                                                               -73.94399
room_type                                                         Entire home/apt
price                                                                          80
minimo_noites                                                                  10
numero_de_reviews                                                               9
ultima_review                                                 2018-11-19 00:00:00
reviews_por_mes                                                               0.1
calculado_host_listings_count                                                   1
disponibilidade_365                                                             0
ano                                                                          2018
mes                                                                            11
dia                                                                            19
Name: 3, dtype: object


disponibilidade_media_por_bairro = dados.groupby('bairro')['disponibilidade_365'].mean()
df_disponibilidade_media = disponibilidade_media_por_bairro.reset_index()
df_disponibilidade_media.rename(columns={'disponibilidade_365': 'disponibilidade_media'}, inplace=True)
df_disponibilidade_media = df_disponibilidade_media.sort_values(by='disponibilidade_media', ascending=False)
print(df_disponibilidade_media)

                         bairro  disponibilidade_media
82               Fort Wadsworth             365.000000
42                   Co-op City             364.000000
215                 Willowbrook             351.000000
65                  Eastchester             333.461538
165                Richmondtown             300.000000
..                          ...                    ...
130         Morningside Heights              43.054913
57            Downtown Brooklyn              39.481928
9    Bay Terrace, Staten Island               0.000000
141                    New Dorp               0.000000
219                     Woodrow               0.000000

[221 rows x 2 columns]


preco_medio_por_bairro = dados.groupby('bairro_group')['price'].mean().reset_index()
preco_medio_por_bairro = preco_medio_por_bairro.sort_values(by='price', ascending=False)
print(round(preco_medio_por_bairro))

    bairro_group  price
2      Manhattan  197.0
1       Brooklyn  124.0
4  Staten Island  115.0
3         Queens  100.0
0          Bronx   87.0


disponibilidade_media_por_bairro_group = dados.groupby('bairro_group')['disponibilidade_365'].mean().reset_index(name='disponibilidade_media')
disponibilidade_media_por_bairro_group_ordenado = disponibilidade_media_por_bairro_group.sort_values(by='disponibilidade_media', ascending=False)
print("Disponibilidade média por bairro_group:")
print(round(disponibilidade_media_por_bairro_group_ordenado))

Disponibilidade média por bairro_group:
    bairro_group  disponibilidade_media
4  Staten Island                  200.0
0          Bronx                  166.0
3         Queens                  144.0
2      Manhattan                  112.0
1       Brooklyn                  100.0


merged_df = pd.merge(disponibilidade_media_por_bairro_group, preco_medio_por_bairro, on='bairro_group')
print(merged_df)

    bairro_group  disponibilidade_media       price
0          Bronx             165.758937   87.496792
1       Brooklyn             100.219122  124.381983
2      Manhattan             111.979410  196.875814
3         Queens             144.451818   99.517649
4  Staten Island             199.678284  114.812332


preco_media_por_tipo = dados.groupby('room_type')['price'].mean().reset_index(name='media_preco')
print("Preço médio por tipo de apartamento:")
print(round(preco_media_por_tipo))

Preço médio por tipo de apartamento:
         room_type  media_preco
0  Entire home/apt        212.0
1     Private room         90.0
2      Shared room         70.0


disponibilidade_media_por_tipo = dados.groupby('room_type')['disponibilidade_365'].mean().reset_index(name='media_disponibilidade')
print("Disponibilidade média por tipo de apartamento:")
print(round(disponibilidade_media_por_tipo))

Disponibilidade média por tipo de apartamento:
         room_type  media_disponibilidade
0  Entire home/apt                  112.0
1     Private room                  111.0
2      Shared room                  162.0


merged_df = pd.merge(preco_media_por_tipo, disponibilidade_media_por_tipo, on='room_type')
print(merged_df)

         room_type  media_preco  media_disponibilidade
0  Entire home/apt   211.794246             111.920304
1     Private room    89.778320             111.192564
2      Shared room    70.127586             162.000862


# Calcular a média da disponibilidade
media_disponibilidade = dados['disponibilidade_365'].mean()

# Dividir os dados em grupos com alta e baixa disponibilidade
alta_disponibilidade = dados[dados['disponibilidade_365'] > media_disponibilidade]
baixa_disponibilidade = dados[dados['disponibilidade_365'] < media_disponibilidade]

# Calcular a média do preço por noite para cada grupo
media_preco_alta_disponibilidade = alta_disponibilidade['price'].mean()
media_preco_baixa_disponibilidade = baixa_disponibilidade['price'].mean()

# Comparar as médias de preço
if media_preco_alta_disponibilidade > media_preco_baixa_disponibilidade:
    print("Apartamentos com alta disponibilidade rendem mais.")
elif media_preco_alta_disponibilidade < media_preco_baixa_disponibilidade:
    print("Apartamentos com baixa disponibilidade rendem mais.")
else:
    print("Não há diferença significativa no rendimento entre apartamentos com alta e baixa disponibilidade.")

Apartamentos com alta disponibilidade rendem mais.


media_disponibilidade = dados['disponibilidade_365'].mean()
print("A média de dias é:", media_disponibilidade)

A média de dias é: 112.77616885507425


media_disponibilidade_por_anuncio = dados.groupby('nome')['disponibilidade_365'].mean().reset_index()
media_disponibilidade_por_anuncio = media_disponibilidade_por_anuncio.sort_values(by='disponibilidade_365', ascending=False)
print(media_disponibilidade_por_anuncio)

                                                    nome  disponibilidade_365
19888                   Futon to crash on New Year in NY                365.0
9994                                Bushwick Artist Loft                365.0
30457    Nice & quiet,clean & neat, all including hostel                365.0
15450                                        Cozy Stay!!                365.0
30401  Newly renovated house with a rooftop near Down...                365.0
...                                                  ...                  ...
25593                           Lincoln Center Apartment                  0.0
25592                 Lincoln Center 1 Bedroom Apartment                  0.0
25589                                           Lily Pad                  0.0
25583                     Like living in an art gallery.                  0.0
0                      1 Bed Apt in Utopic Williamsburg                   0.0

[47904 rows x 2 columns]


preco_medio_por_bairro = dados.groupby('bairro')['price'].mean().reset_index()
top_10_bairros_caros = preco_medio_por_bairro.sort_values(by='price', ascending=False).head(10)
print(top_10_bairros_caros)

                bairro       price
82      Fort Wadsworth  800.000000
219            Woodrow  700.000000
197            Tribeca  490.638418
174           Sea Gate  487.857143
167          Riverdale  442.090909
157       Prince's Bay  409.500000
6    Battery Park City  367.557143
75   Flatiron District  341.925000
161      Randall Manor  336.000000
144               NoHo  295.717949


preco_medio_por_bairro = dados.groupby('nome')['price'].mean().reset_index()
top_10_bairros_caros = preco_medio_por_bairro.sort_values(by='price', ascending=False).head(10)
print(top_10_bairros_caros)

                                                    nome    price
1107                                 1-BR Lincoln Center  10000.0
19866                Furnished room in Astoria apartment  10000.0
26756    Luxury 1 bedroom apt. -stunning Manhattan views  10000.0
40315                                 Spanish Harlem Apt   9999.0
35501                Quiet, Clean, Lit @ LES & Chinatown   9999.0
2213   2br - The Heart of NYC: Manhattans Lower East ...   9999.0
7017   Beautiful/Spacious 1 bed luxury flat-TriBeCa/Soho   8500.0
19383                                      Film Location   8000.0
18040          East 72nd Townhouse by (Hidden by Airbnb)   7703.0
20108                               Gem of east Flatbush   7500.0


top10_por_bairro = dados.groupby('bairro').apply(lambda x: x.nlargest(10, 'price'))
top10_por_bairro = top10_por_bairro.reset_index(drop=True)
top10_nomes_por_bairro = top10_por_bairro[['nome', 'bairro', 'price']]
print(top10_nomes_por_bairro)

                                                   nome    bairro  price
0     Modern/Spacious 4BR +Tent Perfect for LARGE gr...  Allerton    450
1       Spacious 2 level home for Groups sleeps upto 12  Allerton    250
2     ♥♥♥ Entire House with Backyard & Superfast WiF...  Allerton    142
3     *NYC Flat Cold Cozy Private Room w/ Blazin' WiFi*  Allerton    142
4     Beautiful NYC Private Room/Bath, Late Check-In OK  Allerton    142
...                                                 ...       ...    ...
1927    Family Apartment.  Close to city. 3 bed, 2 Bath  Woodside    289
1928  Townhouse apt. Close to city. 3 bedrooms & 2 bath  Woodside    275
1929  Modern 3 Bedroom 2 BATH! 18 min to Times Sq+More!  Woodside    275
1930             Brand New Fully Remodeled Modern House  Woodside    255
1931                                      Living In Art  Woodside    250

[1932 rows x 3 columns]


top10_por_grupo_bairro = dados.groupby('bairro_group').apply(lambda x: x.nlargest(10, 'price'))
top10_por_grupo_bairro = top10_por_grupo_bairro.reset_index(drop=True)
top10_nomes_por_grupo_bairro = top10_por_grupo_bairro[['nome', 'bairro_group', 'price']]
print(top10_nomes_por_grupo_bairro)

                                                 nome   bairro_group  price
0                             "The luxury of Comfort"          Bronx   2500
1                              VJ'S 5 HOUR YACHT TOUR          Bronx   1000
2          New York's Hidden Secret for luxury living          Bronx    800
3                     1 Room in a 2 Bedroom Available          Bronx    680
4                                SedaOn2 Dance Studio          Bronx    670
5                                  Luxury and comfort          Bronx    600
6            ROMANTIC SUNSET YACHT CRUISE & FIREWORKS          Bronx    600
7                         Downtown style in the Bronx          Bronx    500
8     Quiet, Secure, Clean and very Private Apartment          Bronx    500
9   Vie's NY Unique crashpad apts sleep 14, metro*...          Bronx    475
10    Luxury 1 bedroom apt. -stunning Manhattan views       Brooklyn  10000
11                                      Film Location       Brooklyn   8000
12                               Gem of east Flatbush       Brooklyn   7500
13                   SUPER BOWL Brooklyn Duplex Apt!!       Brooklyn   6500
14                   NearWilliamsburg bridge 11211 BK       Brooklyn   5000
15                                           Fulton 2       Brooklyn   5000
16                                         Broadway 1       Brooklyn   5000
17                               Photography Location       Brooklyn   4500
18                     Northside Williamsburg Stunner       Brooklyn   4500
19   Beautiful private Brooklyn room with kitchenette       Brooklyn   4200
20                                1-BR Lincoln Center      Manhattan  10000
21                                 Spanish Harlem Apt      Manhattan   9999
22                Quiet, Clean, Lit @ LES & Chinatown      Manhattan   9999
23  2br - The Heart of NYC: Manhattans Lower East ...      Manhattan   9999
24  Beautiful/Spacious 1 bed luxury flat-TriBeCa/Soho      Manhattan   8500
25          East 72nd Townhouse by (Hidden by Airbnb)      Manhattan   7703
26                70' Luxury MotorYacht on the Hudson      Manhattan   7500
27                   3000 sq ft daylight photo studio      Manhattan   6800
28               Apartment New York \nHell’s Kitchens      Manhattan   6500
29       Luxury TriBeCa Apartment at an amazing price      Manhattan   6500
30                Furnished room in Astoria apartment         Queens  10000
31                      Majestic Mansion LifeStyle :)         Queens   2600
32                       Gorgeous 2 Bedroom apartment         Queens   2350
33                 Room with sofa bed or air mattress         Queens   2000
34                                   wait until later         Queens   2000
35             Mins away to Manhattan Suite Residence         Queens   1800
36   Magnificent Lakeview Home on Kissena Park in NYC         Queens   1500
37         Oceanfront vacation home on Rockaway Beach         Queens   1500
38  2nd floor of 2-family house available for Dece...         Queens   1500
39                            Artist apartment in LIC         Queens   1250
40                            Victorian Film location  Staten Island   5000
41  Central Hall Colonial with Free Parking Bus EX...  Staten Island   1250
42        Victorian with Stunning Views of NYC Harbor  Staten Island   1000
43    Beach House Retreat. 15 minutes from Manhattan.  Staten Island    800
44                      Spacious center hall colonial  Staten Island    700
45        Wedding guests accommodations, 3-7 bedrooms  Staten Island    625
46  Staten Island Resort minutes from ferry! Amazi...  Staten Island    450
47                 large house with inground pool/spa  Staten Island    429
48                         Staten Island NYC Marathon  Staten Island    300
49   FairPlay BNB for alternative life style renters!  Staten Island    300


subset = dados[['price', 'minimo_noites', 'disponibilidade_365']]
scaler = MinMaxScaler()
normalized_data = scaler.fit_transform(subset)
normalized_df = pd.DataFrame(normalized_data, columns=subset.columns)
correlacao = normalized_df.corr()
print(correlacao)

                        price  minimo_noites  disponibilidade_365
price                1.000000       0.042799             0.081833
minimo_noites        0.042799       1.000000             0.144320
disponibilidade_365  0.081833       0.144320             1.000000


covariancia = normalized_df.cov()
print(covariancia)

                        price  minimo_noites  disponibilidade_365
price                0.000577       0.000017             0.000709
minimo_noites        0.000017       0.000270             0.000855
disponibilidade_365  0.000709       0.000855             0.130032


determinacao = correlacao.pow(2)
print(determinacao)

                        price  minimo_noites  disponibilidade_365
price                1.000000       0.001832             0.006697
minimo_noites        0.001832       1.000000             0.020828
disponibilidade_365  0.006697       0.020828             1.000000


# Plotar o gráfico
fig = px.scatter(dados, x='price', y='minimo_noites', color='room_type', size='disponibilidade_365',
                 labels={'price': 'Preço', 'minimo_noites': 'Mínimo de Noites', 'disponibilidade_365': 'Disponibilidade 365'},
                 title='')

# Exibir o gráfico
fig.show()

# X: preço 
# Y: número mínimo de noites
# O tamanho dos pontos representa a disponibilidade_365
# A cor dos pontos representa o room_type


# Plotar o gráfico
fig = px.scatter(dados, x='price', y='minimo_noites', color='bairro_group', size='disponibilidade_365',
                 labels={'price': 'Preço', 'minimo_noites': 'Mínimo de Noites', 'disponibilidade_365': 'Disponibilidade 365'},
                 title='')

# Exibir o gráfico
fig.show()

# X: preço 
# Y: número mínimo de noites
# O tamanho dos pontos representa a disponibilidade_365
# A cor dos pontos representa o grupo de bairros


# Baixar as stopwords (se necessário)
nltk.download('stopwords')
nltk.download('punkt')

[nltk_data] Downloading package stopwords to
[nltk_data]     C:\Users\elain\AppData\Roaming\nltk_data...
[nltk_data]   Package stopwords is already up-to-date!
[nltk_data] Downloading package punkt to
[nltk_data]     C:\Users\elain\AppData\Roaming\nltk_data...
[nltk_data]   Package punkt is already up-to-date!

True


# Calcular o preço médio por bairro
preco_medio_por_bairro = dados.groupby('bairro')['price'].mean().reset_index()

# Ordenar os bairros com base no preço médio em ordem decrescente e selecionar os 10 primeiros
top_10_bairros_caros = preco_medio_por_bairro.sort_values(by='price', ascending=False).head(10)

# Combinar o DataFrame original com os 10 bairros mais caros
dados_top_10_bairros_caros = pd.merge(dados, top_10_bairros_caros, on='bairro')
dados_top_10_bairros_caros

# Selecionar os nomes dos locais (bairros)
nomes_locais = dados_top_10_bairros_caros['nome'].tolist()
nomes_locais

['NOHO/EAST VILLAGE, PRIVATE 1/2 BATH',
 '1 Bedroom in prime Flatiron',
 "Charming 1br in of NYC's best Neighborhood!",
 'Luxury Loft Noho New York City',
 'Sunlit Noho Loft',
 '1-bedroom APT SoHo / NoLITa',
 'Large, Private 1BR in Noho/Nolita/Soho',
 'HUGE 2bdrm LOFT in NOHO/East Vill!',
 'Noho East Vill Lux Bldg w/Gym,Pool ',
 'Large Stylish 1.5 BR Noho/Nolita',
 'Prime E. Village at St. Marks Place',
 'Charming Soho One Bedroom + Loft',
 'Modern Studio Loft in Downtown NYC',
 'Full Floor Loft Style Apt/Noho/Bond',
 'Beautiful, SEXY apartment',
 'Beautiful 1205 ft classic NoHo Loft',
 'Entire 1 Bedroom apartment',
 'Modern Apartment in NOHO / SOHO',
 'NOHO ART LOFT ON LAFAYETTE. BEST LOCATION IN NYC',
 'Cozy Bowery Studio (6, BDFM trains)',
 'Loft on the Bowery w/roofdeck',
 'Mid Nolita Soho Lit. Italy Village',
 'NoHo 2 Bedroom Loft',
 'Noho Broadway 2 Bedroom Loft',
 '1 brd apartment for Easter week in NOHO',
 'Cozy Studio in the Heart of Soho',
 'Huge 2 Bed Loft Apt in Chic Downtown',
 'Private room. Full size bed. 3rd floor. Cooper Sq',
 'Quiet and cozy apartment in Greenwich Village',
 'Bond Street 2 bedroom 2 bath great Noho location',
 'Prime SOHO Luxury penthouse Loft',
 'Stylish Apt in the Heart of Greenwich Village',
 'Quintessential loft location on Bond Street.',
 'Sun Filled 18ft Ceiling Duplex Noho/East Village',
 'Tasteful NOHO 1 bedroom',
 'Classic Manhattan Loft Apartment',
 'ART LOFT/HOME:  DINNERS, GATHERINGS, PHOTO',
 'Charming and Bright 1 bdr apartment in Noho',
 'Artsy Bowery Loft',
 'ALL YOURS FULL APARTMENT\n2 BDM 2 BTH East Village!',
 'Large Alcove Studio in Prime Noho Building',
 'Separate 2 Bedroom Apartment located inside Loft.',
 'Bright Designer NoHo/Greenwich Village Loft',
 'Soho/Nolita historic and central downtown location',
 'Artist Loft.Good Energy.Big Space With Fire Place!',
 'NEW Luxury 1BR Oasis - Most Desirable NYC Location',
 'SoHo LOFT #2  Broadway Av. - Amazing Location!',
 'Upscale living on Lafayette St. NoHO',
 'Bright & Quiet Minutes from Washington Square Park',
 'Perfect Sunny 1BR in the Treetops',
 'Historic New York Home',
 'Large 2 bedroom downtown Loft Apartment',
 'New York City  Apartment',
 'Unique Penthouse',
 'Mesmerized Penthouse',
 'Artist Penthouse',
 'Your New York Penthouse',
 'Elite Penthouse',
 '“One of a kind” Penthouse \n獨一無二的紐約閣樓',
 'Home on Houston - 2 Rooms near SoHo/NoHo/LES',
 'Beautiful Elite Penthouse',
 'Manhattan Penthouse',
 'Beautiful home in the heart of Manhattan.',
 'Original NoHo Loft full of Charm & Character',
 'Brand new luxurious apartment in SoHo!',
 'Mesmerized Penthouse',
 'Entire Loft Apartment in Soho/NoHo/Nolita',
 'The Industrial LOFT #1 NoHo. - Great Location!',
 'The Industrial LOFT #2 NoHo. - Great Location!',
 'Urban Design Sanctuary',
 'Downtown 2500 Sq Ft  Loft.',
 'Modern luxury apartment in the heart of Noho',
 'Beautiful - Art Filled - Sun Filled - Luxurious',
 'Cozy NoHo Studio Apartment',
 'Perfect 1 Bedroom in Nolita',
 'Dapper East Village 1BR w/ Gym, W/D, Doorman, near Subway, by Blueground',
 'Prime Modern 2 BR East Village Apt nearby Subway',
 'Get Inspired in Soho | 1 Bedroom',
 'Stylish Large Gramercy Loft!',
 '21 day Chelsea Apartment rental',
 "Flatiron-Designer's loft",
 'Come stay in super comfy and cozy!',
 'Very Large Loft in Chelsea for  Your Stay!!',
 'Studio in FlatIron, NYC !! ',
 'Best Location btwn Un Sq/ Chelsea',
 'Newly Renovated West Village Privat',
 'Luxury 3 Bd 2Bth w/600 sqft deck',
 '4BD, 2 Bath Apt Flatiron on 6th Ave',
 'Flat Iron 2BD Beautiful Apt ',
 '1 Bedroom Apartment in Gramcery!',
 'Elegant Gramercy Park Studio',
 'NY Duplex Apartment in Flatiron',
 'Flatiron studio next to Madison Pk',
 'Cozy | Spacious | Great Location',
 'Huge, central & artsy Park Ave apt!',
 'Huge Private Room in the BEST location',
 'Loft Space for Events, Meetings & Shoots',
 'Flatiron Studio Loft, Elevator Bldg',
 'Flatiron 1 Bedroom',
 'Chelsea Apt Great Location',
 'Excellent Location Gramercy Studio',
 'NEWLY RENOVATED 1BDR PRIME LOCATION',
 'Luxury Apartment on Park ave',
 'Sleek Loft, Premium Location',
 'Gramercy Apartment',
 'True 1 bedroom apt in Gramercy',
 'Lovely 3 Bedroom, 2 Bath Flatiron Area Loft',
 'Classic Loft in Gramercy/Flatiron - Amazing Value',
 'Madison Square Park - Midtown East LUX Sleeps 6',
 'Beautiful Flatiron 3 Bedroom, 2 Bath Loft',
 'Fabulously Located Comfortable 3 Bedroom Loft',
 '1 bedroom PRIVATE backyard in heart of Flatiron',
 'Huge Flatiron Designer Studio',
 'Spacious artsy apartment near Gramercy/Flatiron',
 'Prime Gramercy Apartment w Balcony',
 'XL Studio Prime location~newly designed~best value',
 'Your home in Flatiron NYC',
 '1400 sq ft modern full floor loft',
 'Beautiful 1 bedroom in the heart of Manhattan!',
 'Flatiron / Gramercy 2 Bed Studio',
 'Private Room in Central Manhattan',
 'Studio Apartment in a Doorman Building',
 'Unique Prime Location NYC  Sleeps 6',
 'Jul 16 - Aug 4 16 1-bed close to Madison Sq. Park',
 'Massive 1200ft² Luxury Loft in Heart of Flatiron',
 '2BR PH Loft and Executive Office Central Manhattan',
 'Fabulous flat iron 2 bedroom 2 bathroom',
 'Gorgeous loft by the Flatiron building',
 'Large, Bright, Open 1-BR Oasis in Gramercy Park',
 'Newly renovated 1br in the heart of NY',
 'Freehand New York- Queen Room',
 'Bright room located in the middle of Manhattan!',
 'Spacious Apartment in the heart of Manhattan!',
 'Cool, comfortable: RIGHT by 3 Subways',
 'Flatiron Oasis Off 5th Ave',
 'Freehand New York- Bunk Room',
 'Gorgeous Gramercy Apt',
 'Private room + office in designer luxury penthouse',
 'Chelsea 1 BR in 4BR Penthouse Apt.',
 'Bright, modern 2 bedroom oasis middle of Flatiron!',
 'Large 1 BR apt in the heart of Flatiron (NYC)',
 'New Flatiron King Bed Apartment',
 'Garden apartment in the heart of Gramercy',
 'Large Private Bedroom with a High-rise View in NYC',
 'CITY VIEWS LARGE APARTMENT',
 'Charming bedroom in the heart of Flatiron',
 'Rad Chelsea 2BR w/ Sundeck, Doorman + Rooftop views by Blueground',
 'Studio Apt in the Heart of Flatiron',
 'Stunning views - prime Gramercy/Flatiron location!',
 'Best Midtown Location! 5 Bedrooms!',
 '4Bed Penthouse! Terrace! Location! 5th Ave',
 'Big Room w Private Entrance in Bohemian Art Loft',
 'Classy and CLEAN Designer Home in NYC 2bath',
 'Mid-Century Modern near Madison Square Park',
 'Elegant 2bdrm apt in the Center of NYC',
 'Duplex Residence with Breathtaking NYC View!',
 'Perfectly Located Apartment in Gramercy',
 'Cheery 1BR in Chelsea w/Gym, Doorman, great Rooftop views by Blueground',
 'Gorgeous PermaGO Private Room in FiDi - 1/2',
 'TriBeCa Amazing River View Loft 3BR',
 'Everyone who stays leaves happy!',
 '2 BED TriBeCa, Beautiful-Renovated!',
 'Beautiful duplex loft with Skylight',
 'Luxury Tribeca 1bdr in Doorman Bldg',
 'Spacious Loft in heart of Tribeca',
 'TriBeCa 2500 Sq Ft w/ Priv Elevator',
 'Tribeca Loft w/ Private Elevator',
 'Authentic Soho Loft (1200 sq ft)',
 'Stunning Sundrenched Tribeca Loft',
 'Suite Spot in Tribeca',
 'AUTHENTIC TRIBECA LOFT NEAR SOHO',
 'Tribeca Loft for Superbowl Wknd',
 'Fantastic Soho/Tribeca Loft',
 'Working Fireplace in TriBeCa loft',
 'Luxury Alcove Studio Apt in TriBeCa',
 'Newly Built Full Bedroom In TriBeCa',
 'HUGE LUX 2FLOOR 2 BDRMSOHO LOFTw/HOME CINEMA',
 '2 Story Loft!! Tribeca/Soho :-) best neighborhood',
 'last minute TRIBECA CONDO AVAILABLE',
 'Modern XL Tribeca apt with terrace ',
 'Stylish 3,000 sq ft loft in TriBeCa',
 'SOHO GALLERY',
 'Best location & Amazing view / Apt in Tribeca',
 'The TriBeCa Apartment — Spacious Living with View',
 'Luxury 2bdr 2bath in Tribeca',
 'PRIVATE Bedroom & bathroom  TRIBECA',
 'Private bedroom in SOHO',
 'Heart of Tribeca next to everything',
 '3BR/1 Ba in TriBeCa w/ outdoor deck',
 'Room Epic View Near Freedom Tower',
 'Brand new in the heart of Tribeca',
 'Trendy TriBeCa SoHo Suite',
 'Private Balcony and views of WTC!',
 'Spacious TriBeCa 3BR w/ Balcony',
 'Heart of Downtown Tribeca',
 'Temporary Stay for 2 Weeks',
 'Beautiful one bedroom in Tribeca!',
 'Huge TriBeCa Loft, Shared Space 1Br',
 'Spacious 2 bedroom loft (1600 sf)',
 'LARGE TRIBECA LOFT WITH ROOF DECK',
 'Room in a loft in Tribeca',
 'Tribeca Loft',
 'Newly Renovated Tribeca 2 br',
 'Cozy Tribeca Studio',
 'SWING FROM THE CHANDELIER',
 'Tribeca launchpad!',
 'Private bedroom in Tribeca',
 'The authentic TriBeCa artist loft experience!',
 'Tribeca/Soho Garden Apartment',
 'Prime Location Luxury Tribeca Loft',
 'Beautiful Tribeca Loft',
 'Beautiful TriBeCa Apartment',
 'Loft',
 'Huge Room in Stunning Pre-war NY Loft',
 'Duane Street, Upscale Tribeca Studio',
 'Duane Street, Upscale Tribeca 1bd',
 'Classic NYC Loft in the heart of Tribeca',
 'Luxe 1-br Large Loft in Tribeca, Entire Apt',
 'Sunny beautiful bedroom in 1 bedroom apartment.',
 'Tribeca Light filled/ Views Apt',
 'Spacious room in huge loft in Tribeca',
 'Tribeca Luxury 2000sf Loft',
 '1 bedroom in Tribeca',
 'Peaceful and Quiet in the heart of TriBeca',
 "Fantastical Artist's loft in Tribeca",
 'Stunning 4 Bed Tribeca Penthouse w/ Huge Terrace!',
 "Inviting Photographer's Loft in Tribeca",
 'Room w/ Private Balcony River View',
 'SOHO,TRIBECA,LOFT,4500sqft.',
 'Spacious light tribeca loft!',
 'Your Fab Home Away from Home awaits You in NYC!!!!',
 'Modern, Beautiful Tribeca 2-Bedroom Loft',
 'Downtown NYC Luxury Apartment - TriBeCa',
 'Big, Bright, Tribeca Studio w/ Doorman & Elevator',
 '2400 Sq Ft Tribeca Loft w/Private Elevator',
 'Big 1-bedroom in Tribeca with landscaped roof deck',
 'Spacious private room in luxury building',
 'Cozy gem in the  heart of TriBeca!!',
 'Bright Quiet Airy Tribeca Loft',
 'Good Vibes Tribeca Loft',
 'Luxe, Central Loft in Downtown Manhattan',
 'Must Love Dogs and Tribeca NYC',
 'Super Cute Family Friendly Cozy Loft Suite For 2-4',
 '3.7 MLN usd 2 bedroom LOFT in TriBeCa (5)',
 '3.5 MLN usd 2 bedroom LOFT in TriBeCa (4)',
 '3.5 MLN usd 2 bedroom LOFT in TriBeCa  (2)',
 'Prime Tribeca Loft',
 'Luxury Skyline Views! Best Panaromic Views Of NYC.',
 'Large luxury apartment in Tribeca',
 'LeonardLoft',
 'Authentic and Open Tribeca Loft',
 'Incredible Tribeca Loft: natural light, spacious',
 'The "TriBeCa LOFT" #3 | A stylish 2 bed apt',
 'The "TriBeCa Loft" #5 | A brand New 2 beds Loft',
 '1 Bedroom Apartment in Tribeca',
 "The 'TriBeCa Loft'  #4  | A stylish 2 bed apt",
 "The 'TriBeCa Loft' #2 | A stylish 2 bed apt",
 'Newly Renovated Studio Apartment',
 'Luxury Tribeca',
 'Beautiful/Spacious 1 bed luxury flat-TriBeCa/Soho',
 'Spacious 1 Bedroom in TriBeCa',
 'Architect Loft,  btw Soho & Tribeca, Private Roof',
 'Luxury Tribeca 1BR w/ Gym, Doorman + Roof deck by Blueground',
 'Private Room in spacious TriBeCa Loft',
 'NY’s Highest End Celebrity Building + Balconies!',
 'Bright, Winged Tribeca Studio w/ Indoor pool, Gym by Blueground',
 'SoBecaTown 1 Bedroom',
 'Loft with View of the Park',
 'Tribeca Luxury Doorman Apartment with Park Views',
 'Classic Tribeca Loft',
 'Sweet Lower Manhattan/Tribeca loft Suite',
 'Penthouse Triplex Jewel - 3000sf',
 'Artist Loft in Tribeca',
 'Apartment in Tribeca with huge private terrace',
 'Huge Room for Nightly Stays Downtown or Monthly',
 'Private room in TRIBECA Loft, elevator into apt!',
 'Perfect Bedroom  W/En Suite Bath & Terrace Access',
 'Sweet Tribeca Studio w/ Great views + Indoor pool by Blueground',
 'TRIBECA/SOHO 2 BEDROOM LUXURY LOFT',
 'Quintessential Tribeca Loft',
 'Classic, yet Unconventional Tribeca New York City!',
 'Tribeca - Large 4 bedroom 2 bathroom for Xmas/NY',
 'Private Room in Charming TriBeCa Loft',
 'Smart Studio in Trendy Tribeca, Indoor pool + Gym by Blueground',
 'Classic, yet Unconventional Tribeca NYC Extra Room',
 'A Luxury Studio in the Heart of Greenwich Village!',
 'Bespoke Tribeca Studio, Indoor pool + Great views by Blueground',
 'Splendid Tribeca 1BR w/ Gym, Doorman + Valet by Blueground',
 'Stunning Tribeca 2BR w/ Indoor pool, Gym, Rooftop by Blueground',
 'Cheery Tribeca Studio w/ Gym + Indoor pool by Blueground',
 'Smart Tribeca Studio w/ Great views + Indoor pool by Blueground',
 'Dandy Tribeca Studio w/ Indoor pool, Views + Gym by Blueground',
 'Stylish Tribeca Studio w/ Gym, Doorman + Valet by Blueground',
 'Well-appointed Tribeca Studio w/ Indoor pool, Gym by Blueground',
 'Shining Tribeca Studio w/ Indoor pool + Gym by Blueground',
 'Homey Tribeca 2BR w/ Great views + Indoor pool by Blueground',
 'Mod Tribeca 1BR w/ Gym, Doorman + Rooftop garden by Blueground',
 'Bright + Mod Tribeca Studio w/ Indoor pool + Gym by Blueground',
 'Stunning Tribeca Studio w/ City views, Gym + Pool by Blueground',
 'Handsome Tribeca Studio w/ Indoor pool + Gym by Blueground',
 'Mod Tribeca Studio w/ Tons of light, Gym, Pool by Blueground',
 'Dazzling Tribeca 2BR w/ Indoor pool + Great views by Blueground',
 'Comfy Tribeca Studio w/ Gym, Doorman + Roof deck by Blueground',
 '2 BR/2BA Modern Apt in Prime TriBeCa Location',
 'Luxurious bedroom in Tribeca - walk to World Trade',
 'Beautiful 6BR + Terrace in SOHO! Dream Location!',
 'Terrific Tribeca 2BR, Indoor pool, Gym + Rooftop by Blueground',
 'Tribeca Loft',
 'Duane Street, Upscale Tribeca Studio',
 'Huge Apartment in TriBeCa',
 'Terrific Tribeca 1BR, Gym, Roof deck, Indoor pool by Blueground',
 'Tribeca Bedroom/Bathroom in Fabulous Penthouse',
 'Harrison Green by (Hidden by Airbnb)',
 'Tribeca Studio w/ Gym + Pool by Blueground',
 '2400 sq. ft. Luxury Tribeca Loft',
 'Beautiful Bright TriBeCa Loft - steps from Soho!',
 'Beautiful, eclectic large Soho-Tribeca 2-Bed Apt',
 '5BR Designer Loft in Soho/TriBeCa! Best Location!',
 'Luxe Modern Tribeca Studio',
 'Studio in doorman building in Tribeca',
 'Homey Tribeca Studio w/ Pool, Roofdeck, Gym & View by Blueground',
 'Gorgeous Tribeca 2BR w/ Amazing city views + Gym by Blueground',
 'Tribeca 3.5 Bedroom Loft',
 'Ideal Tribeca Studio w/ Gym, W/D, Doorman, Pool, View by Blueground',
 'Gorgeous Tribeca Studio w/ Gym, W/D, Doorman, Pool, View, by Blueground',
 'Gorgeous & Spacious Tribeca Loft Style Apartment',
 'Iconic Tribeca Loft',
 'Luxury TriBeCa Apartment at an amazing price',
 'HUGE Tribeca/SOHO Loft - 2,200 Square Feet!!',
 'Charming TriBeCa Loft - Avail for Summer',
 'Adorable TriBeCa neighborhood easy subway access!',
 'Quaint Tribeca Studio w/ Gym, W/D, Doorman, Garage, Valet, by Blueground',
 'Lux Tribeca Studio w/ Pool + Gym + View near City Hall by Blueground',
 "Stunning Luxury Loft in NYC's Best Neighborhood",
 'Luxury & Spacious 1500 ft² MANHATTAN Townhouse',
 '*Unique Master BR in Battery Park!*',
 nan,
 'Space For guest',
 'Downtown, close to every subway!',
 'Luxury Building Huge Studio',
 'Bright Room in Battery Park!',
 'Loft Like Studio in Luxury Building',
 'October SPECIAL!! Enjoy the Holiday in NYC! Sale!',
 'One-of-a-Kind Luxury NYC EPIC VIEW!',
 'Giant Private Room in Battery Park ',
 'Battery Park City Sunny Bedroom ',
 'Statue of Liberty Views Downtown',
 'Amazing 1BR condo with water views',
 'Top Floor Doorman Apt-Water Views',
 'Luxurious 1BR Best Views in NYC! ',
 'Family summer on the Hudson - 1 wk. min. 8/16-9/4',
 '2 Bdrm in Luxury Condo on Hudson',
 'View Waterfront Studio - Manhattan',
 'Charming cozy bedroom, Downtown',
 '2 bed, 2 bath in Battery Park City',
 'Battery Park Monthly rental, great location.',
 'Private Cozy studio NBPC/Tribeca',
 'Large Family Size Apt. Ground Zero',
 'Furnished 2 bed | 2 bath w/ views',
 'Beautiful Luxury Tribeca Apartment',
 'Lower Manhattan Luxury',
 'Luxurious, modern, clean studio apt',
 'Room With a View!',
 'Enjoy One of a Kind Spectacular NYC Views',
 '***Lux Doorman/Furnished,Battery Park ***',
 'Luxury For Less In Downtown Manhattan',
 '**Amazing, Luxury Doorman, VIEW ,Battery Park**',
 'Furnished Battery Park Apartment',
 'Luxurious 1/1.5 (sleeps 4!) in Battery Park!',
 'Parks, Marina and New York skyline in one place.',
 'Downtown Luxury 1 Bedroom 800 sq ft',
 'Super Lux 2BR in Downtown Manhattan',
 'Private Bedroom w River View in Downtown Manhattan',
 'Chambers Street Luxury One Bedroom',
 'Cozy and Warm Stay in Fidi/Battery Park City',
 'Stunning view in TriBeCa luxury 2 bedrooms',
 'Spacious, 1 bdr apartment. Great light and views.',
 'Water views in two directions',
 '5 Star Quiet, Spacious Condo. Freedom Tower view',
 'Home Sweat Home',
 'One Beautiful Sunny Bedroom with Amazing Views',
 'A Night at Anchor Aboard Yacht Ventura',
 'Attractive FiDi 1BR w/ Office nook + Gym, Doorman by Blueground',
 'Cozy waterfront studio in downtown Manhattan.',
 'RENOVATED LUXURY 3BR 3BATH ON LITTLE WEST STREET',
 'Cozy One Bedroom Apartment In Downtown Manhattan',
 'Immaculate and Bright Apartment Downtown',
 'Neat FiDi Studio w/ Gym, Doorman, + Roof deck by Blueground',
 'A spacious 1 bedroom apt in a landmark building',
 'Luxury Apartment with Statue of Liberty view',
 'Studio at Battery Park, Downtown Manhattan',
 '*SUMMER SUBLET*Sunny Penthouse w/ Private Terrace*',
 'Beautiful Luxury Studio w/Exceptional Water View',
 'Cozy apartment w/ Magnificent View!',
 "70' Luxury MotorYacht on the Hudson",
 'Stylish new luxury apartment',
 'Look at that Suite City View!',
 'Harbor View for Two!',
 'Enjoy the Suite Life with a Harbor View!',
 'Magnificent View of the Statue of Liberty!',
 'Harbor View for a King!',
 'Lower Manhattan for the Family!',
 'The City That Never Sleep, with a View!',
 'Water View in Battery Park near Statue of Liberty',
 'Great apartment in downtown NYC for August',
 'Spacious center hall colonial',
 'brooklyn 14 bedroom gated community',
 'Charming house in Coney Island',
 'Comfortable and Spacious Bedroom',
 'Comfortable and Spacious Bedroom B',
 'My place 4 u',
 '"Sea Gate" Sunny, cozy, very clean studio app',
 'seagate pravite house 5 mins away from beach',
 'Three bedroom upscale condo',
 '1 Bedroom/1 Bathroom in Riverdale (close to HIR)',
 'Beautiful apartment with a loft.',
 'Fully furniture huge room in great apt',
 'Well located apartment near NY City',
 '"The luxury of Comfort"',
 'Luxury and comfort',
 "New York's Hidden Secret for luxury living",
 'Spacious Apt with Charm & Character',
 'Quiet, luxury 1BR--15minutes to Manhattan!',
 'Riverdales finest!',
 'Private floor with private half-bath, living area',
 'Affordable Luxury Minutes to Manhattan',
 'Private Loft-style Bedroom & En-suite in S.I., NY',
 'Staten island home not far from ferry to manhattan',
 'Victorian Film location',
 'RELAX & UNWIND - Classy Unique HOUSE for 9 People',
 'Classy Modern 2 Beds for 2 People-Staten Island NY',
 'A happy home',
 'Fully Furnished Spacious Home for 7 People',
 'Nice room near SI Ferry \n15 minutes by car.',
 'Classy Antique Private Bedroom 20-minutes to Ferry',
 'Staten Island retreat.',
 'Happy home 2',
 'Modern Staten Island Townhouse- Welcome home!',
 'Newly Renovated Spacious 2 Bedroom Apartment!',
 'Newly Renovated 3BR Apt,Minutes away from SI Ferry',
 'NYC cozy apartment close to staten island ferry',
 'Happy Home 3',
 'Newly renovated 1 bedroom apartment!',
 'Cute Lofted Apt.',
 "Anna's place  bed and breakfast",
 'Cozy farmhouse in NYC. 2 bdrms; 2 full baths. WiFi',
 'Central Hall Colonial with Free Parking Bus EXP NY',
 'Beach House Retreat. 15 minutes from Manhattan.']


# Tokenização e limpeza dos nomes dos locais
tokens_limpos = []
stop_words = set(stopwords.words('english'))
for nome_local in nomes_locais:
    if isinstance(nome_local, str):  # Verificar se o valor não é NaN
        tokens = word_tokenize(nome_local.lower())  # Tokenização e conversão para minúsculas
        tokens_limpos.extend([token for token in tokens if token not in stop_words and token.isalpha()])

# Calcular a frequência das palavras
frequencia_palavras = pd.Series(tokens_limpos).value_counts()

# Visualizar as palavras mais frequentes
print(frequencia_palavras.head(20))

tribeca       134
loft          108
bedroom        59
studio         55
apartment      55
luxury         48
blueground     35
apt            32
private        30
view           29
gym            28
views          26
location       26
nyc            26
room           25
pool           24
spacious       24
manhattan      23
park           23
beautiful      21
dtype: int64


mapeamento_room_type = {'Entire home/apt': 1, 'Private room': 2, 'Shared room': 3}
dados['room_type_numerico'] = dados['room_type'].map(mapeamento_room_type)
print(dados[['room_type', 'room_type_numerico']].head(20))

          room_type  room_type_numerico
0   Entire home/apt                   1
1      Private room                   2
2   Entire home/apt                   1
3   Entire home/apt                   1
4   Entire home/apt                   1
5      Private room                   2
6      Private room                   2
7      Private room                   2
8   Entire home/apt                   1
9   Entire home/apt                   1
10     Private room                   2
11     Private room                   2
12     Private room                   2
13  Entire home/apt                   1
14  Entire home/apt                   1
15  Entire home/apt                   1
16     Private room                   2
17  Entire home/apt                   1
18  Entire home/apt                   1
19  Entire home/apt                   1


bairro_group_mapping = {'Bronx': 1, 'Brooklyn': 2, 'Manhattan': 3, 'Queens': 4, 'Staten Island': 5}
dados['bairro_group_numerico'] = dados['bairro_group'].map(bairro_group_mapping)
print(dados[['bairro_group', 'bairro_group_numerico']].head(20))

   bairro_group  bairro_group_numerico
0     Manhattan                      3
1     Manhattan                      3
2      Brooklyn                      2
3     Manhattan                      3
4     Manhattan                      3
5      Brooklyn                      2
6     Manhattan                      3
7     Manhattan                      3
8     Manhattan                      3
9     Manhattan                      3
10    Manhattan                      3
11     Brooklyn                      2
12    Manhattan                      3
13    Manhattan                      3
14     Brooklyn                      2
15     Brooklyn                      2
16    Manhattan                      3
17     Brooklyn                      2
18    Manhattan                      3
19     Brooklyn                      2


bairros_unicos = dados['bairro'].unique()
mapeamento_bairros = {bairro: i+1 for i, bairro in enumerate(bairros_unicos)}
dados['bairro_numerico'] = dados['bairro'].map(mapeamento_bairros)
print(dados[['bairro', 'bairro_numerico']].head(20))

                bairro  bairro_numerico
0              Midtown                1
1               Harlem                2
2         Clinton Hill                3
3          East Harlem                4
4          Murray Hill                5
5   Bedford-Stuyvesant                6
6       Hell's Kitchen                7
7      Upper West Side                8
8            Chinatown                9
9      Upper West Side                8
10      Hell's Kitchen                7
11         South Slope               10
12     Upper West Side                8
13        West Village               11
14        Williamsburg               12
15         Fort Greene               13
16             Chelsea               14
17       Crown Heights               15
18         East Harlem                4
19        Williamsburg               12


dados.head(10)


dados.columns

Index(['id', 'nome', 'host_id', 'host_name', 'bairro_group', 'bairro',
       'latitude', 'longitude', 'room_type', 'price', 'minimo_noites',
       'numero_de_reviews', 'ultima_review', 'reviews_por_mes',
       'calculado_host_listings_count', 'disponibilidade_365', 'ano', 'mes',
       'dia', 'room_type_numerico', 'bairro_group_numerico',
       'bairro_numerico'],
      dtype='object')


new_data = dados[['host_id', 'latitude', 'longitude', 'minimo_noites',
                        'numero_de_reviews', 'reviews_por_mes', 'disponibilidade_365',
                        'room_type_numerico', 'bairro_group_numerico', 'bairro_numerico',
                  'price']]
print( new_data.head(10))

   host_id  latitude  longitude  minimo_noites  numero_de_reviews  \
0     2845  40.75362  -73.98377              1                 45   
1     4632  40.80902  -73.94190              3                  0   
2     4869  40.68514  -73.95976              1                270   
3     7192  40.79851  -73.94399             10                  9   
4     7322  40.74767  -73.97500              3                 74   
5     7356  40.68688  -73.95596             45                 49   
6     8967  40.76489  -73.98493              2                430   
7     7490  40.80178  -73.96723              2                118   
8     7549  40.71344  -73.99037              1                160   
9     7702  40.80316  -73.96545              5                 53   

   reviews_por_mes  disponibilidade_365  room_type_numerico  \
0             0.38                  355                   1   
1             0.72                  365                   2   
2             4.64                  194                   1   
3             0.10                    0                   1   
4             0.59                  129                   1   
5             0.40                    0                   2   
6             3.47                  220                   2   
7             0.99                    0                   2   
8             1.33                  188                   1   
9             0.43                    6                   1   

   bairro_group_numerico  bairro_numerico  price  
0                      3                1    225  
1                      3                2    150  
2                      2                3     89  
3                      3                4     80  
4                      3                5    200  
5                      2                6     60  
6                      3                7     79  
7                      3                8     79  
8                      3                9    150  
9                      3                8    135


X = new_data.drop(columns=['price']) # atributos de entrada
y = new_data['price'] # atributo alvo - número

# holdout: 30% teste, 70% treino
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=42)

# normalize
# https://scikit-learn.org/stable/modules/generated/sklearn.preprocessing.normalize.html

# padronização dos valores
scaler = StandardScaler()
X_train_scaled = scaler.fit_transform(X_train)
X_test_scaled = scaler.transform(X_test)


# https://scikit-learn.org/stable/modules/linear_model.html
modelos = {
    'Regressão Linear': LinearRegression(),    
    'Regressão Ridge': Ridge(alpha=0.5),
    'Regressão Lasso': Lasso(alpha=0.5),
    'Árvore de Decisão': DecisionTreeRegressor(random_state=42)
    
    # Esses modelos estavam travando meu computador
    # 'Random Forest': RandomForestRegressor(random_state=42),
    # 'SVR': SVR(kernel='rbf'),    
}


resultados = pd.DataFrame(columns=['Nome', 'MAE', 'MSE', 'RMSE', 'R2'])
for nome, modelo in modelos.items():    
    modelo.fit(X_train_scaled, y_train) # fit = treinar
    y_pred = modelo.predict(X_test_scaled) # predict = predizer
    
    #modelo.fit(X_train, y_train)
    #y_pred = modelo.predict(X_test)
    
    # Calcular as métricas
    # mpe = mean_percentage_error(y_test, y_pred)
    mse = mean_squared_error(y_test, y_pred)
    mae = mean_absolute_error(y_test, y_pred)    
    rmse = np.sqrt(mse)
    mape = mean_absolute_percentage_error(y_test, y_pred)    
    r2 = r2_score(y_test, y_pred)  
    
    temp_df = pd.DataFrame({'Nome': [nome],
                            'MAE': [mae],
                            'MSE': [mse],
                            'RMSE': [rmse],
                            'MAPE': [mape],
                            'R2': [r2]})   
    
    resultados = pd.concat([resultados, temp_df], ignore_index=True)    
    
    predicted = np.round(y_pred,2)    
    saidas = pd.DataFrame([y_test, predicted]).T
    saidas.columns = ["Real", "Predito"]    
    
    # Exibir os resultados
    # print(f'{nome}:')    
    # print(f'MAE = {mae}')
    # print(f'MSE = {mse}')
    # print(f'RMSE = {rmse}')
    # print(f'R² = {r2}')
    # print("Preços Preditos:", predicted)
    # print()
    
print(resultados)
print(saidas)

                Nome        MAE            MSE        RMSE        R2  \
0   Regressão Linear  72.773423   41327.801197  203.292403  0.103697   
1    Regressão Ridge  72.773273   41327.791053  203.292378  0.103697   
2    Regressão Lasso  72.596626   41325.549188  203.286864  0.103746   
3  Árvore de Decisão  87.632286  100959.264572  317.740876 -1.189569   

           MAPE  
0  2.751135e+13  
1  2.751158e+13  
2  2.774616e+13  
3  3.960491e+13  
        Real  Predito
879    110.0    115.0
47429   40.0      NaN
15394   57.0      NaN
24100  375.0      NaN
16332  195.0      NaN
...      ...      ...
9370    55.0     75.0
44291  239.0      NaN
9164   250.0     99.0
11841  125.0    255.0
46957   97.0      NaN

[14669 rows x 2 columns]


# Definir as métricas a serem usadas para avaliação
scorer_mse = make_scorer(mean_squared_error)
scorer_mape = make_scorer(lambda y_true, y_pred: np.mean(np.abs((y_true - y_pred) / y_true)) * 100)
scorer_mpe = make_scorer(lambda y_true, y_pred: np.mean((y_true - y_pred) / y_true) * 100)
scorer_rmse = make_scorer(lambda y_true, y_pred: np.sqrt(mean_squared_error(y_true, y_pred)))
scorer_r2 = make_scorer(r2_score)
scorer_mae = make_scorer(mean_absolute_error)

# Inicializar os modelos de regressão
modelos = {
    'Regressão Linear': LinearRegression(),
    'Árvore de Decisão': DecisionTreeRegressor(random_state=42),
    'Regressão Ridge': Ridge(alpha=0.5),
    'Regressão Lasso': Lasso(alpha=0.5)
    # 'SVR': SVR(kernel='rbf'),
    #'Random Forest': RandomForestRegressor(random_state=42),
}


# Normalizar os dados
scaler = StandardScaler()
X = scaler.fit_transform(X)

# Avaliar cada modelo utilizando validação cruzada de 10 folds
for nome, modelo in modelos.items():
    # Calcular os scores de validação cruzada para o modelo
    scores_mse = cross_val_score(modelo, X, y, cv=10, scoring=scorer_mse)
    scores_mape = cross_val_score(modelo, X, y, cv=10, scoring=scorer_mape)
    scores_mpe = cross_val_score(modelo, X, y, cv=10, scoring=scorer_mpe)
    scores_rmse = cross_val_score(modelo, X, y, cv=10, scoring=scorer_rmse)
    scores_r2 = cross_val_score(modelo, X, y, cv=10, scoring=scorer_r2)
    scores_mae = cross_val_score(modelo, X, y, cv=10, scoring=scorer_mae)
    
    # Obter as previsões durante a validação cruzada
    predicted = cross_val_predict(modelo, X, y, cv=10)
    predicted = np.round(predicted,2)
    
    # Exibir os resultados
    print(f'{nome}:')    
    print(f'Média do MSE: {np.mean(scores_mse)}')
    print(f'Média do MAPE: {np.mean(scores_mape)}')
    print(f'Média do MPE: {np.mean(scores_mpe)}')
    print(f'Média do RMSE: {np.mean(scores_rmse)}')
    print(f'Média do R2: {np.mean(scores_r2)}')
    print(f'Média do MAE: {np.mean(scores_mae)}')
    print(f'Preços Preditos: {predicted}')    
    print()

Regressão Linear:
Média do MSE: 52587.78284205892
Média do MAPE: inf
Média do MPE: nan
Média do RMSE: 226.7016923959988
Média do R2: 0.08910569942267962
Média do MAE: 75.0418408436335
Preços Preditos: [265.03 163.69 128.84 ... 207.51  23.04 126.85]

Árvore de Decisão:
Média do MSE: 129740.76757506207
Média do MAPE: inf
Média do MPE: -inf
Média do RMSE: 348.29066856026736
Média do R2: -1.2621643939139893
Média do MAE: 99.15215782184539
Preços Preditos: [ 995.  100.  150. ...  160.  125. 3000.]

Regressão Ridge:
Média do MSE: 52587.78203390505
Média do MAPE: inf
Média do MPE: nan
Média do RMSE: 226.7016885967048
Média do R2: 0.0891057507539286
Média do MAE: 75.041731996586
Preços Preditos: [265.03 163.69 128.84 ... 207.51  23.04 126.85]

Regressão Lasso:
Média do MSE: 52583.42273181339
Média do MAPE: inf
Média do MPE: nan
Média do RMSE: 226.6903138019618
Média do R2: 0.08922637334881739
Média do MAE: 74.84379669251177
Preços Preditos: [263.31 162.38 131.79 ... 206.55  23.72 126.26]

	id	host_id	latitude	longitude	price	minimo_noites	numero_de_reviews	reviews_por_mes	calculado_host_listings_count	disponibilidade_365
count	4.889400e+04	4.889400e+04	48894.000000	48894.000000	48894.000000	48894.000000	48894.000000	38842.000000	48894.000000	48894.000000
mean	1.901753e+07	6.762139e+07	40.728951	-73.952169	152.720763	7.030085	23.274758	1.373251	7.144005	112.776169
std	1.098288e+07	7.861118e+07	0.054529	0.046157	240.156625	20.510741	44.550991	1.680453	32.952855	131.618692
min	2.595000e+03	2.438000e+03	40.499790	-74.244420	0.000000	1.000000	0.000000	0.010000	1.000000	0.000000
25%	9.472371e+06	7.822737e+06	40.690100	-73.983070	69.000000	1.000000	1.000000	0.190000	1.000000	0.000000
50%	1.967743e+07	3.079553e+07	40.723075	-73.955680	106.000000	3.000000	5.000000	0.720000	1.000000	45.000000
75%	2.915225e+07	1.074344e+08	40.763117	-73.936273	175.000000	5.000000	24.000000	2.020000	2.000000	227.000000
max	3.648724e+07	2.743213e+08	40.913060	-73.712990	10000.000000	1250.000000	629.000000	58.500000	327.000000	365.000000

	ano	frequencia
0	2019	35261
1	2018	6049
2	2017	3205
4	2016	2707
6	2015	1393
8	2014	199
7	2013	48
5	2012	25
3	2011	7

	nome	count
1707	1 Bed Apt in Utopic Williamsburg	1
6032	2-3 bedroom UWS garden triplex	1
4782	3 bedroom loft in Williamsburg	1
2628	A charming Space in Brooklyn	1
5048	AMAZING TIME SQUARE!!BRICK WALLS!!	1
...	...	...
36011	한성 韓城 Han C (2F)	1
25457	️CENTRALLY LOCATED️- Great for Families + Groups	1
26718	ｳｨﾘｱﾑｽﾞﾊﾞｰｸﾞのかわいいお部屋です2	1
46442	ﾏﾝﾊｯﾀﾝ､駅から徒歩4分でどこに行くのにも便利な場所!女性の方希望,ｷﾚｲなお部屋｡	1
2829	NaN	16

	host_id	count
30564	219517861	327
21708	107434423	232
8391	30283594	121
26733	137358866	103
25207	12243051	96
...	...	...
15604	23727216	1
15603	89211125	1
15602	19928013	1
15601	1017772	1
37456	68119814	1

Desafio¶

Dicionário dos dados¶

1. Faça uma análise exploratória dos dados (EDA), demonstrando as principais características entre as variáveis e apresentando algumas hipóteses de negócio relacionadas. Seja criativo!¶

	id	nome	host_id	host_name	bairro_group	bairro	latitude	longitude	room_type	price	minimo_noites	ultima_review	reviews_por_mes	calculado_host_listings_count	disponibilidade_365
48884	36482809	Stunning Bedroom NYC! Walking to Central Park!!	131529729	Kendall	Manhattan	East Harlem	40.79633	-73.93605	Private room	75	2	NaN	NaN	2	353
48885	36483010	Comfy 1 Bedroom in Midtown East	274311461	Scott	Manhattan	Midtown	40.75561	-73.96723	Entire home/apt	200	6	NaN	NaN	1	176
48886	36483152	Garden Jewel Apartment in Williamsburg New York	208514239	Melki	Brooklyn	Williamsburg	40.71232	-73.94220	Entire home/apt	170	1	NaN	NaN	3	365
48887	36484087	Spacious Room w/ Private Rooftop, Central loca...	274321313	Kat	Manhattan	Hell's Kitchen	40.76392	-73.99183	Private room	125	4	NaN	NaN	1	31
48888	36484363	QUIT PRIVATE HOUSE	107716952	Michael	Queens	Jamaica	40.69137	-73.80844	Private room	65	1	NaN	NaN	2	163
48889	36484665	Charming one bedroom - newly renovated rowhouse	8232441	Sabrina	Brooklyn	Bedford-Stuyvesant	40.67853	-73.94995	Private room	70	2	NaN	NaN	2	9
48890	36485057	Affordable room in Bushwick/East Williamsburg	6570630	Marisol	Brooklyn	Bushwick	40.70184	-73.93317	Private room	40	4	NaN	NaN	2	36
48891	36485431	Sunny Studio at Historical Neighborhood	23492952	Ilgar & Aysel	Manhattan	Harlem	40.81475	-73.94867	Entire home/apt	115	10	NaN	NaN	1	27
48892	36485609	43rd St. Time Square-cozy single bed	30985759	Taz	Manhattan	Hell's Kitchen	40.75751	-73.99112	Shared room	55	1	NaN	NaN	6	2
48893	36487245	Trendy duplex in the very heart of Hell's Kitchen	68119814	Christophe	Manhattan	Hell's Kitchen	40.76404	-73.98933	Private room	90	7	NaN	NaN	1	23

	id	nome	host_id	host_name	bairro_group	bairro	latitude	longitude	room_type	price	minimo_noites	numero_de_reviews	ultima_review	reviews_por_mes	calculado_host_listings_count	disponibilidade_365
0	2595	Skylit Midtown Castle	2845	Jennifer	Manhattan	Midtown	40.75362	-73.98377	Entire home/apt	225	1	45	2019-05-21	0.38	2	355
1	3647	THE VILLAGE OF HARLEM....NEW YORK !	4632	Elisabeth	Manhattan	Harlem	40.80902	-73.94190	Private room	150	3	0	NaN	NaN	1	365
2	3831	Cozy Entire Floor of Brownstone	4869	LisaRoxanne	Brooklyn	Clinton Hill	40.68514	-73.95976	Entire home/apt	89	1	270	2019-07-05	4.64	1	194
3	5022	Entire Apt: Spacious Studio/Loft by central park	7192	Laura	Manhattan	East Harlem	40.79851	-73.94399	Entire home/apt	80	10	9	2018-11-19	0.10	1	0
4	5099	Large Cozy 1 BR Apartment In Midtown East	7322	Chris	Manhattan	Murray Hill	40.74767	-73.97500	Entire home/apt	200	3	74	2019-06-22	0.59	1	129
5	5121	BlissArtsSpace!	7356	Garon	Brooklyn	Bedford-Stuyvesant	40.68688	-73.95596	Private room	60	45	49	2017-10-05	0.40	1	0
6	5178	Large Furnished Room Near B'way	8967	Shunichi	Manhattan	Hell's Kitchen	40.76489	-73.98493	Private room	79	2	430	2019-06-24	3.47	1	220
7	5203	Cozy Clean Guest Room - Family Apt	7490	MaryEllen	Manhattan	Upper West Side	40.80178	-73.96723	Private room	79	2	118	2017-07-21	0.99	1	0
8	5238	Cute & Cozy Lower East Side 1 bdrm	7549	Ben	Manhattan	Chinatown	40.71344	-73.99037	Entire home/apt	150	1	160	2019-06-09	1.33	4	188
9	5295	Beautiful 1br on Upper West Side	7702	Lena	Manhattan	Upper West Side	40.80316	-73.96545	Entire home/apt	135	5	53	2019-06-22	0.43	1	6

	res	frequencia
1	0000-00-00	10052
9	2019-06-23	1413
13	2019-07-01	1359
38	2019-06-30	1341
6	2019-06-24	875
...	...	...
913	2013-12-14	1
1422	2015-04-12	1
931	2014-11-30	1
934	2014-06-02	1
1764	2018-03-29	1

	mes	frequencia
5	12	1770
2	11	1158
4	10	1545
9	9	1535
7	8	1667
1	7	5937
3	6	13589
0	5	14753
8	4	2109
10	3	1288
11	2	770
6	1	2773

	dia	frequencia
7	31	992
17	30	2305
8	29	1280
9	28	1289
16	27	1307
25	26	1210
19	25	1120
4	24	1537
6	23	2021
3	22	1271
0	21	1196
29	20	1065
1	19	11231
22	18	933
20	17	909
18	16	1144
13	15	985
14	14	746
12	13	720
15	12	712
28	11	716
30	10	817
5	9	902
27	8	899
21	7	1466
26	6	1240
2	5	1378
23	4	1110
24	3	1390
11	2	1997
10	1	3006

	id	nome	host_id	host_name	bairro_group	bairro	latitude	longitude	room_type	price	minimo_noites	numero_de_reviews	ultima_review	reviews_por_mes	calculado_host_listings_count	disponibilidade_365	ano	mes	dia
38292	30181691	Sonder \| 180 Water \| Incredible 2BR + Rooftop	219517861	Sonder (NYC)	Manhattan	Financial District	40.70637	-74.00645	Entire home/apt	302	29	0	2019-05-19	0.72	327	309	2019	5	19
38293	30181945	Sonder \| 180 Water \| Premier 1BR + Rooftop	219517861	Sonder (NYC)	Manhattan	Financial District	40.70771	-74.00641	Entire home/apt	229	29	1	2019-05-29	0.73	327	219	2019	5	29
38587	30347708	Sonder \| 180 Water \| Charming 1BR + Rooftop	219517861	Sonder (NYC)	Manhattan	Financial District	40.70743	-74.00443	Entire home/apt	232	29	1	2019-05-21	0.60	327	159	2019	5	21
39768	30937590	Sonder \| The Nash \| Artsy 1BR + Rooftop	219517861	Sonder (NYC)	Manhattan	Murray Hill	40.74792	-73.97614	Entire home/apt	262	2	8	2019-06-09	1.86	327	91	2019	6	9
39769	30937591	Sonder \| The Nash \| Lovely Studio + Rooftop	219517861	Sonder (NYC)	Manhattan	Murray Hill	40.74771	-73.97528	Entire home/apt	255	2	14	2019-06-10	2.59	327	81	2019	6	10
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
47690	35871510	Sonder \| 116 John \| Vibrant Studio + Fitness Room	219517861	Sonder (NYC)	Manhattan	Financial District	40.70818	-74.00631	Entire home/apt	135	29	0	2019-05-19	0.72	327	339	2019	5	19
47691	35871511	Sonder \| 116 John \| Vibrant 1BR + Fitness Room	219517861	Sonder (NYC)	Manhattan	Financial District	40.70691	-74.00682	Entire home/apt	165	29	0	2019-05-19	0.72	327	342	2019	5	19
47692	35871515	Sonder \| 116 John \| Stunning 1BR + Rooftop	219517861	Sonder (NYC)	Manhattan	Financial District	40.70772	-74.00673	Entire home/apt	165	29	0	2019-05-19	0.72	327	347	2019	5	19
47813	35936418	Sonder \| 116 John \| Polished Studio + Gym	219517861	Sonder (NYC)	Manhattan	Financial District	40.70840	-74.00518	Entire home/apt	699	29	0	2019-05-19	0.72	327	327	2019	5	19
47820	35937891	Sonder \| 116 John \| Simple Studio + Gym	219517861	Sonder (NYC)	Manhattan	Financial District	40.70707	-74.00557	Entire home/apt	699	29	0	2019-05-19	0.72	327	341	2019	5	19

	host_id	bairro_group	num_anuncios
34757	219517861	Manhattan	327
29495	107434423	Manhattan	230
19636	30283594	Manhattan	121
12846	12243051	Manhattan	96
14482	16098958	Manhattan	96
...	...	...	...
13379	13499885	Brooklyn	1
13378	13497325	Brooklyn	1
13377	13496782	Brooklyn	1
13375	13490977	Brooklyn	1
37571	274321313	Manhattan	1

	host_id	bairro	num_anuncios
35447	219517861	Financial District	218
9948	7503643	Greenpoint	52
13080	12243051	Financial District	51
30770	120762452	Murray Hill	50
35450	219517861	Murray Hill	50
...	...	...	...
13593	13451102	Upper West Side	1
13591	13440686	Bedford-Stuyvesant	1
13590	13425453	Clinton Hill	1
13589	13425452	Greenwich Village	1
38311	274321313	Hell's Kitchen	1